package com.glo.spreadsheet2db.services;

import java.io.IOException;
import java.net.MalformedURLException;
import java.net.URL;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import com.glo.spreadsheet2db.model.Spreadsheet;
import com.glo.spreadsheet2db.model.SpreadsheetCell;
import com.google.gdata.client.spreadsheet.SpreadsheetService;
import com.google.gdata.data.spreadsheet.CellEntry;
import com.google.gdata.data.spreadsheet.CellFeed;
import com.google.gdata.data.spreadsheet.SpreadsheetEntry;
import com.google.gdata.data.spreadsheet.SpreadsheetFeed;
import com.google.gdata.data.spreadsheet.WorksheetEntry;
import com.google.gdata.data.spreadsheet.WorksheetFeed;
import com.google.gdata.util.AuthenticationException;
import com.google.gdata.util.ServiceException;

public abstract class AbstractSpreadsheet2DbService implements
		Spreadsheet2DbService {

	private final URL SPREADSHEET_FEED_URL;
	private final Pattern r1c1LocationRegex = Pattern.compile("R(\\d+)C(\\d+)");

	public AbstractSpreadsheet2DbService() {
		super();
		try {
			// Define the URL to request. This should never change.
			SPREADSHEET_FEED_URL = new URL(
					"https://spreadsheets.google.com/feeds/spreadsheets/private/full");
		} catch (MalformedURLException e) {
			// should never happen but still...
			throw new RuntimeException(e);
		}
	}

	private SpreadsheetService getSpreadsheetService(String username,
			String password) throws AuthenticationException {
		SpreadsheetService service = new SpreadsheetService("spreadsheet2db");
		service.setUserCredentials(username, password);
		return service;
	}

	public List<String> getSpreadsheetList(String username, String password)
			throws IOException, ServiceException {

		SpreadsheetService service = getSpreadsheetService(username, password);

		// Make a request to the API and get all spreadsheets.
		SpreadsheetFeed feed = service.getFeed(SPREADSHEET_FEED_URL,
				SpreadsheetFeed.class);
		List<SpreadsheetEntry> spreadsheets = feed.getEntries();

		List<String> titles = new ArrayList<String>();

		// Iterate through all of the spreadsheets returned
		for (SpreadsheetEntry spreadsheet : spreadsheets) {
			// Print the title of this spreadsheet to the screen
			titles.add(spreadsheet.getTitle().getPlainText());
		}

		return titles;
	}

	public Spreadsheet importSpreadsheet(String username, String password,
			String spreadsheetName) throws IOException, ServiceException {

		Spreadsheet spreadsheet = null;

		SpreadsheetService service = getSpreadsheetService(username, password);

		// Make a request to the API and get all spreadsheets.
		SpreadsheetFeed feed = service.getFeed(SPREADSHEET_FEED_URL,
				SpreadsheetFeed.class);
		List<SpreadsheetEntry> spreadsheets = feed.getEntries();

		SpreadsheetEntry spreadsheetEntry = null;

		for (SpreadsheetEntry entry : spreadsheets) {
			if (entry.getTitle().getPlainText().equals(spreadsheetName)) {
				spreadsheetEntry = entry;
				break;
			}
		}

		if (spreadsheetEntry == null) {
			return null;
		}

		// Get the first worksheet of the first spreadsheet.
		WorksheetFeed worksheetFeed = service.getFeed(
				spreadsheetEntry.getWorksheetFeedUrl(), WorksheetFeed.class);
		List<WorksheetEntry> worksheets = worksheetFeed.getEntries();
		WorksheetEntry worksheetEntry = worksheets.get(0);

		spreadsheet = new Spreadsheet(worksheetEntry.getRowCount(),
				worksheetEntry.getColCount());

		// Fetch the cell feed of the worksheet.
		URL cellFeedUrl = worksheetEntry.getCellFeedUrl();
		CellFeed cellFeed = service.getFeed(cellFeedUrl, CellFeed.class);

		List<SpreadsheetCell> cells = new ArrayList<SpreadsheetCell>();
		
		// Iterate through each cell, printing its value.
		for (CellEntry cell : cellFeed.getEntries()) {
			String r1c1Location = cell.getId().substring(cell.getId().lastIndexOf('/') + 1);
			// Print the cell's address in R1C1 notation
			Matcher m = r1c1LocationRegex.matcher(r1c1Location);
			
			int columnIndex = 0;
			int rowIndex = 0;
			
			if ( m.matches() ) {
				rowIndex = Integer.parseInt( m.group(1) );
				columnIndex = Integer.parseInt( m.group(2) );
			}

			SpreadsheetCell spreadsheetCell = new SpreadsheetCell(cell.getCell().getValue(), columnIndex-1, rowIndex-1);
			cells.add(spreadsheetCell);
			spreadsheet.attachCells(cells);
		}

		return spreadsheet;
	}

	public boolean existsSpreadsheet(String userName, String password,String sheetName) throws IOException, ServiceException {
		List<String> list = getSpreadsheetList(userName, password);
		for (String sheet : list) {
			if(sheet.equals(sheetName)) {
				return true;
			}
		}
		return false;
	}
	
}
